package com.javaweb.dao.impl;

import com.javaweb.aop.NPECatch;
import com.javaweb.common.StringUtils;
import com.javaweb.dao.PermDao;
import com.javaweb.model.Permission;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/9/24 22:43
 */

/**
 * todo: 实现全部如下方法
 */
@Repository
@Slf4j
@NPECatch
public class PermDaoImpl implements PermDao {
    @Autowired
    private Connection conn;

    @Override
    public Integer insert(Permission perm) {
        String sql = "INSERT INTO tb_permission (name,url) VALUES(?,?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setString(1, perm.getName());
            ps.setString(2, perm.getUrl());
            return ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute insert permission error: " + e.getMessage()) ;
        }
        return 0 ;
    }

    @Override
    public Integer insertRolePerm(int roleId, int permId) {
        String sql = "INSERT INTO tb_role_per (role_id, per_id) VALUES (?,?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
            ps.setInt(1, roleId) ;
            ps.setInt(2, permId) ;
            return ps.executeUpdate() ;
        }catch (SQLException e) {
            log.error("execute insertRolePerm error: " + e.getMessage()) ;
        }
        return 0;
    }

    @Override
    public Permission selectOne(String name, String url) {
        String sql = "select * from tb_permission where name=? AND url=?" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setString(1, name);
            ps.setString(2, url);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Permission permission=new Permission();
                permission.setPerId(rs.getInt(1));
                permission.setName(rs.getString(2));
                permission.setUrl(rs.getString(3));
                return  permission;
            }
        } catch (SQLException e) {
            log.error("execute selectOne error: " + e.getMessage()) ;
        }
        return null;
    }

    @Override
    public Permission selectOneById(int permId) {
        String sql = "select * from tb_permission where per_id=?" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setInt(1,permId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Permission permission=new Permission();
                permission.setPerId(rs.getInt(1));
                permission.setName(rs.getString(2));
                permission.setUrl(rs.getString(3));
                return  permission;
            }
        } catch (SQLException e) {
            log.error("execute selectOneById error: " + e.getMessage()) ;
        }
        return null;
    }

    @Override
    public List<Permission> selectPermsByRoleName(String roleName) {
        // SQL 查询语句，获取与角色名称对应的所有权限
        String sql = "SELECT p.* FROM tb_permission p " +
                "JOIN tb_role_per rp ON p.per_id = rp.per_id " +
                "JOIN tb_role r ON rp.role_id = r.role_id " +
                "WHERE r.name = ?";
        List<Permission> permissions = new ArrayList<>();
        try (PreparedStatement ps = this.conn.prepareStatement(sql)) {
            ps.setString(1, roleName);  // 替换 SQL 语句中的占位符 ?
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Permission permission = new Permission();
                permission.setPerId(rs.getInt(1));
                permission.setName(rs.getString(2));
                permission.setUrl(rs.getString(3));
                permissions.add(permission);
            }
        } catch (SQLException e) {
            log.error("execute selectPermsByRoleName error: " + e.getMessage());
        }
        return permissions;
    }

    @Override
    public List<Permission> selectPermsByRoleId(int roleId) {
        // SQL 查询语句，获取与角色名称对应的所有权限
        String sql = "SELECT p.* FROM tb_permission p " +
                "JOIN tb_role_per rp ON p.per_id = rp.per_id " +
                "JOIN tb_role r ON rp.role_id = r.role_id " +
                "WHERE r.role_id = ?";
        List<Permission> permissions = new ArrayList<>();
        try (PreparedStatement ps = this.conn.prepareStatement(sql)) {
            ps.setInt(1,roleId);  // 替换 SQL 语句中的占位符 ?
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Permission permission = new Permission();
                permission.setPerId(rs.getInt(1));
                permission.setName(rs.getString(2));
                permission.setUrl(rs.getString(3));
                permissions.add(permission);
            }
        } catch (SQLException e) {
            log.error("execute selectPermsByRoleId error: " + e.getMessage());
        }
        return permissions;
    }

    @Override
    public Integer deleteById(int permId) {
        String deleteRolePermSql = "DELETE FROM tb_role_per WHERE per_id = ?";
        String deletePermSql = "DELETE FROM tb_permission WHERE per_id = ?";

        int rowsAffected = 0; // 用于记录受影响的行数

        try {
            // 关闭自动提交，开始事务
            this.conn.setAutoCommit(false);

            // 删除角色与权限的关联
            try (PreparedStatement ps1 = this.conn.prepareStatement(deleteRolePermSql)) {
                ps1.setInt(1, permId); // 设置权限 ID
                rowsAffected += ps1.executeUpdate(); // 执行删除，记录受影响的行数
            }

            // 删除权限
            try (PreparedStatement ps2 = this.conn.prepareStatement(deletePermSql)) {
                ps2.setInt(1, permId); // 设置权限 ID
                rowsAffected += ps2.executeUpdate(); // 执行删除，记录受影响的行数
            }

            // 提交事务
            this.conn.commit();
        } catch (SQLException e) {
            // 出错时回滚事务
            try {
                if (this.conn != null) {
                    this.conn.rollback();
                }
            } catch (SQLException rollbackEx) {
                log.error("execute rollbackEx error: " + rollbackEx.getMessage()) ;
            }
            log.error("execute deleteById error: " + e.getMessage());
        } finally {
            // 恢复自动提交状态
            try {
                if (this.conn != null) {
                    this.conn.setAutoCommit(true);
                }
            } catch (SQLException ex) {
                log.error("execute AutoCommit error: " + ex.getMessage()) ;
            }
        }

        return rowsAffected; // 返回受影响的行数
    }
}
